package com.wn.excel.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.wn.excel.config.ExcelProperties;
import com.wn.excel.config.ExportTableProperties;
import com.wn.excel.config.TableConvertProperties;
import com.wn.excel.constant.ExcelConst;
import com.wn.excel.convert.DataTypeDescGenerator;
import com.wn.excel.exception.Attempt;
import com.wn.excel.handler.WorkBookHandler;
import com.wn.excel.mapper.TableMapper;
import com.wn.excel.model.Column;
import com.wn.excel.model.ColumnMapping;
import com.wn.excel.model.Table;
import com.wn.excel.model.TableIndex;
import com.wn.excel.service.TableService;
import com.wn.excel.util.ReflectUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * @FileName: TableServiceImpl
 * @Description: 表结构服务接口实现
 * @Author: MaWenHao
 * @Created: 2020/3/2 - 19:21
 * @Version: V1.0
 * @Company: 东方微银科技（北京）有限公司
 */
@Slf4j
@Service
public class TableServiceImpl implements TableService {

    private static int currentRowNum;

    @Autowired
    private ExportTableProperties exportTableProperties;

    @Autowired
    private TableConvertProperties convertProperties;

    @Autowired
    private ExcelProperties excelProperties;

    @Autowired
    private TableMapper tableMapper;

    @Autowired
    private WorkBookHandler handler;

    @Autowired
    private DataTypeDescGenerator dataTypeGenerators;

    /**
     * 导出表结构,将Oracle转为mysql
     */
    @Override
    public void exportTableFromOracleToMysql() {
        // 当前登录用户名
        final String username = convertProperties.getUsername();

        try (
                BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(
                        new FileOutputStream(convertProperties.getFilePath()), "UTF-8")
                )
        ) {
            // 查询所有表名
            List<Table> tables = tableMapper.selectAllTableNames();
            if (tables.isEmpty()) {
                return;
            }
            log.info("读取表名成功");

            tables.stream()
                    // 根据配置规则过滤表名
                    .filter(this::matching)
                    .forEach(Attempt.accept(table -> {
                        String tableName = table.getTableName();
                        // 查询表结构
                        List<Column> columns = tableMapper.selectTableStructureByTableName(tableName, username);
                        // 查询表索引
                        List<TableIndex> indexList = tableMapper.selectIndexByTableNameAndOwner(tableName, username);
                        // 查询表主键
                        List<String> primaryKeyList = tableMapper.selectPrimaryKeyByTableName(table.getTableName());
                        log.info("查询表结构成功>>>>" + tableName);
                        // 创建新的创建表语句
                        String sql = this.genTableSql(table, columns, indexList, primaryKeyList);
                        log.info("数据表<" + tableName + ">初始化sql生成成功: \r\n" + sql);
                        // 写入文件
                        bw.write(sql);
                        bw.newLine();
                    }));
            bw.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据oracle 生成mysql表创建语句
     *
     * @param table          表名
     * @param columns        列信息
     * @param indexList      索引信息
     * @param primaryKeyList 主键信息
     * @return
     */
    private String genTableSql(Table table, List<Column> columns, List<TableIndex> indexList, List<String> primaryKeyList) {
        StringBuilder sb = new StringBuilder();
        // 生成创建表sql
        String tableSql = this.createTableSql(table, columns);
        // 生成创建索引sql
        String indexSql = this.createIndexSql(table, indexList);
        // 创建主键索引
        String primary = this.createPrimarySql(table, primaryKeyList);

        sb.append(tableSql)
                .append("\r\n");
        if (StrUtil.isNotBlank(indexSql)) {
            sb.append(indexSql);
        }
        if (StrUtil.isNotBlank(primary)) {
            sb.append(primary)
                    .append("\r\n\r\n");
        }

        return sb.toString();
    }

    /**
     * 创建 主键索引sql
     *
     * @param table
     * @param primaryKeyList
     * @return
     */
    private String createPrimarySql(Table table, List<String> primaryKeyList) {
        if (CollectionUtil.isEmpty(primaryKeyList)) {
            return null;
        }
        StringBuilder sb = new StringBuilder();
        sb.append("ALTER TABLE ")
                .append(this.formatColumn(table.getTableName()))
                .append(" ADD PRIMARY KEY (");
        primaryKeyList.stream().forEach(column -> sb.append(this.formatColumn(column)).append(", "));

        sb.delete(sb.lastIndexOf(","), sb.length())
                .append(");");

        return sb.toString();
    }

    /**
     * 创建 索引sql
     *
     * @param table
     * @param indexList
     * @return
     */
    private String createIndexSql(Table table, List<TableIndex> indexList) {
        Map<String, List<TableIndex>> indices = this.convert(indexList);

        StringBuilder sb = new StringBuilder();
        indices.values().stream().map(tableIndices -> this.convert(table, tableIndices)).forEach(sb::append);
        return sb.toString();
    }

    /**
     * 创建表sql
     *
     * @param table
     * @param columns
     * @return
     */
    private String createTableSql(Table table, List<Column> columns) {
        StringBuilder sb = new StringBuilder();
        sb.append("DROP TABLE IF EXISTS ")
                .append(this.formatColumn(table.getTableName()))
                .append(";\r\n")
                .append("CREATE TABLE ")
                .append(this.formatColumn(table.getTableName()))
                .append("  (\r\n");
        columns.stream().map(this::convert).forEach(sb::append);
        sb.delete(sb.lastIndexOf(","), sb.length());
        sb.append(") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ");
        if (StrUtil.isNotBlank(table.getTableComment())) {
            sb.append(" COMMENT ")
                    .append(this.formatValue(table.getTableComment()));
        }
        sb.append(";");
        return sb.toString();
    }

    private Map<String, List<TableIndex>> convert(List<TableIndex> indexList) {
        Map<String, List<TableIndex>> map = new HashMap<>(8);
        indexList.forEach(index -> {
            List<TableIndex> tableIndexList = map.get(index.getIndexName());
            if (CollectionUtil.isEmpty(tableIndexList)) {
                List<TableIndex> element = new ArrayList<>();
                element.add(index);
                map.put(index.getIndexName(), element);
                return;
            }
            tableIndexList.add(index);
        });
        return map;
    }

    /**
     * 生成索引创建sql
     *
     * @param table
     * @param indexList
     * @return
     */
    private String convert(Table table, List<TableIndex> indexList) {
        StringBuilder sb = new StringBuilder();
        sb.append("ALTER TABLE ")
                .append(this.formatColumn(table.getTableName()))
                .append(" ADD ");
        if (StrUtil.equals("UNIQUE", indexList.get(0).getIndexType())) {
            sb.append(" UNIQUE ");
        }
        sb.append(" INDEX ")
                .append(indexList.get(0).getIndexName())
                .append("(");
        indexList.stream().forEach(index -> sb.append(this.formatColumn(index.getColumnName())).append(", "));
        sb.delete(sb.lastIndexOf(","), sb.length())
                .append(");\r\n");

        return sb.toString();
    }

    /**
     * 生成每个字段的创建语句
     *
     * @param column
     * @return
     */
    private String convert(Column column) {
        StringBuilder sb = new StringBuilder("\t");
        sb.append(this.formatColumn(column.getColumnName()))
                .append(" ")
                .append(this.getDataType(column));
        if (StrUtil.equals("N", column.getNullable())) {
            sb.append(" NOT NULL ");
        }
        if (StrUtil.isNotEmpty(column.getDataDefault())) {
            sb.append(" DEFAULT ");
            // 日期默认值特殊处理
            if (StrUtil.equals("sysdate", column.getDataDefault())) {
                sb.append("now()");
            } else {
                sb.append(this.formatValue(column.getDataDefault()));
            }
        }
        if (StrUtil.isNotBlank(column.getComments())) {
            sb.append(" COMMENT ")
                    .append(this.formatValue(column.getComments()));
        }
        sb.append(",\r\n");
        return sb.toString();
    }

    /**
     * 生成类型精度描述
     *
     * @param column
     * @return
     */
    private String getDataType(Column column) {
        // 类型映射
        column = this.typeMapping(column);
        // 生成数据类型描述
        String columnDataType = dataTypeGenerators.gen(column);
        return columnDataType;
    }

    /**
     * oralce类型装mysql
     *
     * @param column
     * @return
     */
    private Column typeMapping(Column column) {
        // 将oracle数据类型转为mysql
        Map<String, String> typeMapping = convertProperties.getTypeMapping();
        if (StrUtil.equals("NUMBER", column.getDataType())) {
            if (StrUtil.isEmpty(column.getDataPrecision()) && StrUtil.isEmpty(column.getDataScale())) {
                if (Integer.valueOf(column.getDataLength()) <= 11) {
                    column.setDataType("INT");
                } else {
                    column.setDataType("BIGINT");
                }
            }
        }
        String type = typeMapping.get(column.getDataType().toLowerCase());
        if (StrUtil.isNotEmpty(type)) {
            column.setDataType(type);
        }
        // 转为大写
        column.setDataType(column.getDataType().toUpperCase());
        return column;
    }

    private String formatValue(String value) {
        value = value.trim();
        // 特殊字符转义处理
        if (value.startsWith("'") && value.endsWith("'")) {
            value = value.substring(1, value.length() - 1);
        }
        value = value.replace("'", "\\'")
                .replace("\"", "\\\"");
        StringBuilder sb = new StringBuilder();
        sb.append("'")
                .append(value)
                .append("'");
        return sb.toString();
    }

    private String formatColumn(String columnName) {
        StringBuilder sb = new StringBuilder();
        sb.append("`")
                .append(columnName)
                .append("`");
        return sb.toString();
    }


    /**
     * 导出表结构
     */
    @Override
    public void exportTableStructure() {
        // 当前登录用户名
        final String username = exportTableProperties.getUsername();
        // 当前行
        currentRowNum = 0;

        try (
                HSSFWorkbook wb = handler.createWorkBook();
                OutputStream fos = new FileOutputStream(exportTableProperties.getFilePath());
        ) {
            // 查询所有表名
            List<Table> tables = tableMapper.selectAllTableNames();
            if (tables.isEmpty()) {
                return;
            }
            log.debug("读取表名成功");

            // 创建要写入的sheet页
            HSSFSheet sheet = handler.createSheet(wb, exportTableProperties.getSheetName());
            // 设置默认样式
            handler.setWorkBookStyle(wb, 0);

            tables.stream()
                    // 根据配置规则过滤表名
                    .filter(this::matching)
                    .forEach(table -> {
                        String tableName = table.getTableName();
                        // 查询表结构
                        List<Column> columns = tableMapper.selectTableStructureByTableName(tableName, username);
                        log.debug("查询表结构成功>>>>" + tableName);
                        // 写入excel
                        this.tableStructInfoToExcel(table, columns, wb);
                        log.debug("数据表<" + tableName + ">编译成功");
                    });

            // 写入文件
            wb.write(fos);
            log.debug("表结构写入excel成功!!!");

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 将表结构写入Excel
     *
     * @param table   表实体
     * @param columns 列名集合
     * @param wb      工作簿
     */
    private void tableStructInfoToExcel(Table table, List<Column> columns, HSSFWorkbook wb) {
        if (columns.isEmpty()) {
            return;
        }

        // 表名
        String tableName = table.getTableName();
        // 要展示的列名配置
        List<ColumnMapping> targetColumnRule = exportTableProperties.getColumnMapping();
        // 总列数
        int len = targetColumnRule.size() + ExcelConst.SKIP;
        // 写入头部
        this.writeHeaderToExcel(table, wb, len);
        log.debug("数据表<" + tableName + ">头部编译成功");
        // 写入内容
        this.writeContentToExcel(columns, wb, targetColumnRule);
        log.debug("数据表<" + tableName + ">内容编译成功");
        // 写入底部
        this.writeBottomToExcel(tableName, wb, len);
        log.debug("数据表<" + tableName + ">底部编译成功");

        currentRowNum += excelProperties.getRowSkip();
    }

    /**
     * 写入底部
     */
    private void writeBottomToExcel(String tableName, HSSFWorkbook wb, int len) {
        // 样式
        HSSFCellStyle style = handler.getBottomStyle(wb);

        // 写入总记录数
        if (exportTableProperties.isExportTotalCount()) {
            // 根据表名查询总记录数
            long count = tableMapper.selectTotalCountByTableName(tableName);
            this.writeTopRow(wb, ExcelConst.TOTAL_COUNT, String.valueOf(count), len, style);
        }

        // 写入说明
        if (exportTableProperties.isExportExtComment()) {
            this.writeTopRow(wb, ExcelConst.TABLE_COMMENTS, null, len, style);
        }
    }

    /**
     * 写入内容部分
     *
     * @param columns 列集合
     * @param wb      工作簿对象
     */
    private void writeContentToExcel(List<Column> columns, HSSFWorkbook wb, List<ColumnMapping> columnRule) {

        // 写入表头
        this.writeTableTop(wb, columnRule);
        // 写入类容
        this.writeTableContent(wb, columnRule, columns);
    }

    /**
     * 写入表内容
     *
     * @param wb         工作簿对象
     * @param columnRule 列名展示规则
     * @param columns    列集合
     */
    private void writeTableContent(HSSFWorkbook wb, List<ColumnMapping> columnRule, List<Column> columns) {
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFCellStyle contentStyle = handler.getContentStyle(wb);

        int rowSize = columns.size();
        for (int i = 0; i < rowSize; i++) {
            // 每一列的结构对象
            Column column = columns.get(i);
            // 创建行
            HSSFRow row = sheet.createRow(currentRowNum);
            row.setHeightInPoints(excelProperties.getContentRowHigh());

            // 写入第一列
            HSSFCell firstCell = row.createCell(0);
            firstCell.setCellValue(i + 1);
            firstCell.setCellStyle(contentStyle);
            int cellSize = columnRule.size();
            for (int j = 0; j < cellSize; j++) {
                ColumnMapping columnMapping = columnRule.get(j);
                String columnName = columnMapping.getColumnName();
                try {
                    String columnValue;
                    if (ExcelConst.DATA_TYPE.equals(columnName)) {
                        columnValue = this.getColumnDataType(column);
                    } else {
                        columnValue = (String) ReflectUtils.getFieldValue(columnName, column);
                    }
                    HSSFCell cell = row.createCell(j + ExcelConst.SKIP);
                    cell.setCellValue(columnValue);
                    cell.setCellStyle(contentStyle);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }

            currentRowNum++;
        }
    }

    /**
     * 获取数据类型
     *
     * @param column
     * @return
     */
    private String getColumnDataType(Column column) {
        StringBuilder sb = new StringBuilder();
        if (StringUtils.isEmpty(column.getDataPrecision()) || StringUtils.isEmpty(column.getDataScale())) {
            return sb.append(column.getDataType())
                    .append(ExcelConst.LEFT_BRACKET)
                    .append(column.getDataLength())
                    .append(ExcelConst.RIGHT_BRACKET)
                    .toString();
        }
        return sb.append(column.getDataType())
                .append(ExcelConst.LEFT_BRACKET)
                .append(column.getDataPrecision())
                .append(ExcelConst.COMMA)
                .append(column.getDataScale())
                .append(ExcelConst.RIGHT_BRACKET)
                .toString();
    }

    /**
     * 写入表头
     */
    private void writeTableTop(HSSFWorkbook wb, List<ColumnMapping> columnRule) {
        HSSFSheet sheet = wb.getSheetAt(0);
        // 创建表头行
        HSSFRow row = sheet.createRow(currentRowNum);
        HSSFCellStyle headerStyle = handler.getHeaderStyle(wb);
        row.setHeightInPoints(excelProperties.getHeaderRowHigh());

        // 写入第一列
        HSSFCell firstCell = row.createCell(0);
        firstCell.setCellValue(ExcelConst.COLUMN_COUNT);
        firstCell.setCellStyle(headerStyle);
        // 写入字段列
        int len = columnRule.size();
        for (int i = 0; i < len; i++) {
            ColumnMapping columnMapping = columnRule.get(i);
            HSSFCell cell = row.createCell(i + ExcelConst.SKIP);
            cell.setCellValue(columnMapping.getChineseName());
            cell.setCellStyle(headerStyle);
        }
        currentRowNum++;
    }

    /**
     * 写入头部内容
     *
     * @param table 表实体
     * @param wb    工作簿对象
     */
    private void writeHeaderToExcel(Table table, HSSFWorkbook wb, int len) {
        HSSFSheet sheet = wb.getSheetAt(0);
        // 样式
        HSSFCellStyle style = handler.getTopStyle(wb);

        // 写入中文表名
        this.writeCOntentAndMergeCell(table.getTableComment(), wb, 0, len);

        // 写入表名
        this.writeTopRow(wb, ExcelConst.TABLE_NAME, table.getTableName(), len, style);

        // 写入数据库用户
        if (exportTableProperties.isExportUsername()) {
            this.writeTopRow(wb, ExcelConst.DATA_BASE_USERNAME, exportTableProperties.getUsername(), len, style);
        }

        // 获取主键
        if (exportTableProperties.isExportPrimaryKey()) {
            List<String> primaryKeyList = tableMapper.selectPrimaryKeyByTableName(table.getTableName());
            String primaryKeyStr = this.convertListToString(primaryKeyList);
            // 写入主键
            this.writeTopRow(wb, ExcelConst.PRIMARY_KEY, primaryKeyStr, len, style);
        }
    }

    /**
     * 写入中文表名，合并列
     *
     * @param value 中文表名
     * @param wb    工作簿对象
     * @param start 起始列
     * @param len   总例数
     */
    private void writeCOntentAndMergeCell(String value, HSSFWorkbook wb, int start, int len) {
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.createRow(currentRowNum);

        HSSFCellStyle titleStyle = handler.getTitleStyle(wb);
        row.setHeightInPoints(excelProperties.getTitleRowHigh());

        // 创建单元格
        for (int i = 0; i < len; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(titleStyle);
        }
        row.getCell(0).setCellValue(value);
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(currentRowNum, currentRowNum, 0, len - 1));
        currentRowNum++;
    }

    /**
     * 写入头部行
     *
     * @param wb    工作簿对象
     * @param key   名称
     * @param value 值
     * @param len   总列数
     * @param style 样式
     */
    private void writeTopRow(HSSFWorkbook wb, String key, String value, int len, HSSFCellStyle style) {
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.createRow(currentRowNum);
        // 设置行高
        row.setHeightInPoints(excelProperties.getTopRowHigh());

        // 创建单元格
        for (int i = 0; i < len; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
        }
        row.getCell(0).setCellValue(key);
        row.getCell(ExcelConst.SKIP).setCellValue(value);
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(currentRowNum, currentRowNum, ExcelConst.SKIP, len - 1));
        currentRowNum++;
    }

    /**
     * 将主键转换为字符传
     *
     * @param primaryKey 主键集合
     * @return 主键字符串
     */
    private String convertListToString(List<String> primaryKey) {
        StringBuilder sb = new StringBuilder();
        primaryKey.stream().forEach(str -> sb.append(str).append(ExcelConst.COMMA));
        if (primaryKey.size() > 0) {
            return sb.substring(0, sb.length() - 1);
        }
        return sb.toString();
    }

    /**
     * 匹配符合条件的表名
     *
     * @param table 表实体
     * @return 是否匹配
     */
    private boolean matching(Table table) {
        // 获取表名过滤规则
        List<String> filter = convertProperties.getFilter();
        if (filter.isEmpty()) {
            return true;
        }
        // 匹配符合规则的表
        long count = filter.stream().map(String::toUpperCase).map(Pattern::compile).filter(pattern -> pattern.matcher(table.getTableName()).find()).count();
        if (count > 0) {
            return true;
        }
        return false;
    }
}

